import datetime
import json

import cx_Oracle
import pandas as pd

"""查询物料系统推送订单在入库单查不到的问题"""

user = "pigpos"
passwd = "P1g#2023pos"


def find_eroor_data():
    sql = "  select account_code from MAS_ACCOUNT_CODE   where  account_code in (  select account_code from MAS_ACCOUNT_CODE WHERE  REGEXP_LIKE(desc_loc, '[^[:print:]]', 'n') )"
    listener = 'CPGCNXL.cpchina.cn/CPGCNXL'
    conn = cx_Oracle.connect(user, passwd, listener)
    cursor = conn.cursor()
    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    cursor.close()
    conn.close()
    return js


def find_correct_data(account_code, cursor):
    sql = "  select * from MAS_ACCOUNT_CODE   where  account_code in ('{}') and not REGEXP_LIKE(desc_loc, '[^[:print:]]', 'n')".format(
        account_code)

    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))

    list = json.loads(js)
    if len(list):
        return list[0]['DESC_LOC']
    else:
        return None


if __name__ == '__main__':
    # 查询乱码的数据
    data = find_eroor_data()
    list = json.loads(data)

    listener = 'CTCNZQF.cpchina.cn/CTCNZQF'
    conn = cx_Oracle.connect(user, passwd, listener)
    cursor = conn.cursor()
    loss_data = []
    for d in list:
        account_code = d['ACCOUNT_CODE']
        # print(account_code)
        desc_loc = find_correct_data(account_code, cursor)
        # print(desc_loc)
        if desc_loc is not None:
            print(
                "update MAS_ACCOUNT_CODE set desc_loc = '{}' where account_code = '{}';".format(desc_loc, account_code))
        else:
            loss_data.append(account_code)

    excel_data = {}
    excel_data['account_code'] = loss_data
    df = pd.DataFrame(excel_data)
    path = "C:/Users/yangjianzhang/Desktop/"
    df.to_excel(path + '缺失的代码.xlsx', index=False)
    # 生成update语句
    cursor.close()
    conn.close()
    pass
